#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into oe_dws.dws_attendance_daycount
with morning_att as (
    select distinct class_id,
                    signin_date,
                    student_id -- 早上出勤且没迟到学生id
    from oe_dwb.dwb_check_detail
    where cast(substring(signin_time, 12, 19) as time) >= date_add('minute', -40, cast(morning_begin_time as time))
      and cast(substring(signin_time, 12, 19) as time) <= date_add('minute', 10, cast(morning_begin_time as time))
      and class_date is not null
      and signin_date between use_begin_date and use_end_date),
     morning_late as (
         select distinct class_id,
                         signin_date,
                         student_id -- 早上非正常时间打卡学生id
         from oe_dwb.dwb_check_detail
         where cast(substring(signin_time, 12, 19) as time) > date_add('minute', 10, cast(morning_begin_time as time))
           and cast(substring(signin_time, 12, 19) as time) <= cast(morning_end_time as time)
           and class_date is not null
           and signin_date between use_begin_date and use_end_date),
     morning_late_count as (
         select ml.class_id,
                ml.signin_date,
                count(ml.student_id) as morning_late_count -- 早上迟到学生人数
         from morning_late ml
                  left join morning_att ma on ml.student_id = ma.student_id
         where ma.student_id is null
         group by ml.class_id, ml.signin_date),
     morning_check_count as (
         select class_id,
                signin_date,
                count(student_id) as morning_check_count
         from morning_att
         group by class_id, signin_date),
     morning_count as (
         select coalesce(mcc.class_id, mlc.class_id)                               as class_id,
                coalesce(mcc.signin_date, mlc.signin_date)                         as signin_date,
                coalesce(morning_check_count, 0) + coalesce(morning_late_count, 0) as morning_attendance_count, -- 早上出勤人数
                coalesce(morning_late_count, 0)                                    as morning_late_count
         from morning_check_count mcc
                  full join morning_late_count mlc
                            on mcc.class_id = mlc.class_id),
     afternoon_att as (
         select distinct class_id,
                         signin_date,
                         student_id -- 中午出勤且没迟到学生id
         from oe_dwb.dwb_check_detail
         where cast(substring(signin_time, 12, 19) as time) >=
               date_add('minute', -40, cast(afternoon_begin_time as time))
           and cast(substring(signin_time, 12, 19) as time) <=
               date_add('minute', 10, cast(afternoon_begin_time as time))
           and class_date is not null
           and signin_date between use_begin_date and use_end_date),
     afternoon_late as (
         select distinct class_id,
                         signin_date,
                         student_id -- 中午非正常时间打卡学生id
         from oe_dwb.dwb_check_detail
         where cast(substring(signin_time, 12, 19) as time) > date_add('minute', 10, cast(afternoon_begin_time as time))
           and cast(substring(signin_time, 12, 19) as time) <= cast(afternoon_end_time as time)
           and class_date is not null
           and signin_date between use_begin_date and use_end_date),
     afternoon_late_count as (
         select ml.class_id,
                ml.signin_date,
                count(ml.student_id) as afternoon_late_count -- 中午迟到学生人数
         from afternoon_late ml
                  left join afternoon_att ma on ml.student_id = ma.student_id
         where ma.student_id is null
         group by ml.class_id, ml.signin_date),
     afternoon_check_count as (
         select class_id,
                signin_date,
                count(student_id) as afternoon_check_count
         from afternoon_att
         group by class_id, signin_date),
     afternoon_count as (
         select coalesce(mcc.class_id, mlc.class_id)       as class_id,
                coalesce(mcc.signin_date, mlc.signin_date) as signin_date,
                coalesce(afternoon_check_count, 0) +
                coalesce(afternoon_late_count, 0)          as afternoon_attendance_count, -- 中午出勤人数
                coalesce(afternoon_late_count, 0)          as afternoon_late_count
         from afternoon_check_count mcc
                  full join afternoon_late_count mlc
                            on mcc.class_id = mlc.class_id),
     evening_att as (
         select distinct class_id,
                         signin_date,
                         student_id -- 晚上出勤且没迟到学生id
         from oe_dwb.dwb_check_detail
         where cast(substring(signin_time, 12, 19) as time) >= date_add('minute', -40, cast(evening_begin_time as time))
           and cast(substring(signin_time, 12, 19) as time) <= date_add('minute', 10, cast(evening_begin_time as time))
           and class_date is not null
           and signin_date between use_begin_date and use_end_date),
     evening_late as (
         select distinct class_id,
                         signin_date,
                         student_id -- 晚上非正常时间打卡学生id
         from oe_dwb.dwb_check_detail
         where cast(substring(signin_time, 12, 19) as time) > date_add('minute', 10, cast(evening_begin_time as time))
           and cast(substring(signin_time, 12, 19) as time) <= cast(evening_end_time as time)
           and class_date is not null
           and signin_date between use_begin_date and use_end_date),
     evening_late_count as (
         select ml.class_id,
                ml.signin_date,
                count(ml.student_id) as evening_late_count -- 晚上迟到学生人数
         from evening_late ml
                  left join evening_att ma on ml.student_id = ma.student_id
         where ma.student_id is null
         group by ml.class_id, ml.signin_date),
     evening_check_count as (
         select class_id,
                signin_date,
                count(student_id) as evening_check_count
         from evening_att
         group by class_id, signin_date),
     evening_count as (
         select coalesce(mcc.class_id, mlc.class_id)                               as class_id,
                coalesce(mcc.signin_date, mlc.signin_date)                         as signin_date,
                coalesce(evening_check_count, 0) + coalesce(evening_late_count, 0) as evening_attendance_count, -- 晚上出勤人数
                coalesce(evening_late_count, 0)                                    as evening_late_count
         from evening_check_count mcc
                  full join evening_late_count mlc
                            on mcc.class_id = mlc.class_id)
select distinct cd.class_date                                              as date_code,
                cd.class_id,
                cd.content,
                cssc.studying_student_count,
                morning_attendance_count,
                morning_attendance_count * 1.00 / studying_student_count   as morning_attendance_ratio,
                morning_late_count,
                morning_late_count * 1.00 / studying_student_count         as morning_late_count,
                afternoon_attendance_count,
                afternoon_attendance_count * 1.00 / studying_student_count as afternoon_attendance_ratio,
                afternoon_late_count,
                afternoon_late_count * 1.00 / studying_student_count       as afternoon_late_ratio,
                evening_attendance_count,
                evening_attendance_count * 1.00 / studying_student_count   as evening_attendance_ratio,
                evening_late_count,
                evening_late_count * 1.00 / studying_student_count         as evening_late_ratio
from oe_dwb.dwb_check_detail cd
         left join oe_dwb.dwb_class_studying_student_count cssc
                   on cd.class_id = cssc.class_id and cd.class_date = cssc.studying_date
         left join morning_count mc on cd.class_id = mc.class_id
         left join afternoon_count ac on cd.class_id = ac.class_id
         left join evening_count ec on cd.class_id = ec.class_id
where cd.class_date is not null;

insert into oe_dws.dws_leave_daycount
with leave_id as (select distinct class_id,
                                  class_date,
                                  case
                                      when begin_time <= concat(cast(class_date as varchar), ' ', morning_end_time) and
                                           end_time >= concat(cast(class_date as varchar), ' ', morning_begin_time)
                                          then student_id
                                      end as morning_leave_id,
                                  case
                                      when begin_time <=
                                           concat(cast(class_date as varchar), ' ', afternoon_end_time) and
                                           end_time >= concat(cast(class_date as varchar), ' ', afternoon_begin_time)
                                          then student_id
                                      end as afternoon_leave_id,
                                  case
                                      when begin_time <= concat(cast(class_date as varchar), ' ', evening_end_time) and
                                           end_time >= concat(cast(class_date as varchar), ' ', evening_begin_time)
                                          then student_id
                                      end as evening_leave_id
                  from oe_dwb.dwb_leave_detail
                  where audit_state = 1
                    and cancel_state = 0
                    and valid_state = 1
                    and class_date is not null),
     leave_count as (select class_id,
                            class_date,
                            count(morning_leave_id)   as morning_leave_count,
                            count(afternoon_leave_id) as afternoon_leave_count,
                            count(evening_leave_id)   as evening_leave_count
                     from leave_id
                     group by class_id, class_date)
select distinct ld.class_date                                              as date_code,
                ld.class_id,
                ld.content,
                cssc.studying_student_count,
                morning_leave_count                                        as morning_leave_count,
                morning_leave_count * 1.00 / cssc.studying_student_count   as morning_leave_ratio,
                afternoon_leave_count                                      as afternoon_leave_count,
                afternoon_leave_count * 1.00 / cssc.studying_student_count as afternoon_leave_ratio,
                evening_leave_count                                        as evening_leave_count,
                evening_leave_count * 1.00 / cssc.studying_student_count   as evening_leave_ratio
from oe_dwb.dwb_leave_detail ld
         left join leave_count lc on ld.class_id = lc.class_id and ld.class_date = lc.class_date
         left join oe_dwb.dwb_class_studying_student_count cssc
                   on ld.class_id = cssc.class_id and ld.class_date = cssc.studying_date
where audit_state = 1
  and cancel_state = 0
  and valid_state = 1
  and ld.class_date is not null;"






